package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Categoria;
import model.Pais;

public class CategoriasDaoImpl implements CategoriasDao{

	Connection c;
	
	public CategoriasDaoImpl(){
		GenericDao gDao = new GenericDao();
		c = gDao.getConnection();
	}
	
	@Override
	public String cadastraCategorias(Categoria cat, Pais p) throws SQLException {
		String sql = "{call sp_cat (?,?,?,?,?)}";
		CallableStatement cs = c.prepareCall(sql);
		cs.setString(1, "I");
		cs.setNull(2, java.sql.Types.INTEGER);
		cs.setString(3, cat.getTipo());
		cs.setInt(4, p.getId());
		cs.registerOutParameter(5, java.sql.Types.VARCHAR);
		cs.execute();
		String saida = cs.getString(5);
		cs.close();
		return saida;
	}

	@Override
	public String editaCategorias(Categoria cat, Pais p) throws SQLException {
		String sql = "{call sp_cat (?,?,?,?,?)}";
		CallableStatement cs = c.prepareCall(sql);
		cs.setString(1, "U");
		cs.setInt(2, cat.getId());
		cs.setString(3, cat.getTipo());
		cs.setInt(4, p.getId());
		cs.registerOutParameter(5, java.sql.Types.VARCHAR);
		cs.execute();
		String saida = cs.getString(5);
		cs.close();
		return saida;
	}

	@Override
	public String excluiCategorias(Categoria cat, Pais p) throws SQLException {
		String sql = "{call sp_cat (?,?,?,?,?)}";
		CallableStatement cs = c.prepareCall(sql);
		cs.setString(1, "D");
		cs.setInt(2, cat.getId());		
		cs.setNull(3, java.sql.Types.VARCHAR);
		cs.setInt(4, p.getId());
		cs.registerOutParameter(5, java.sql.Types.VARCHAR);
		cs.execute();
		String saida = cs.getString(5);
		cs.close();
		return saida;
	}

	@Override
	public List<Categoria> consultaListaCategorias()
			throws SQLException {
		List<Categoria> listaCat = new ArrayList<Categoria>();
		String sql = "SELECT id, tipo FROM categorias";
		PreparedStatement ps = c.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()){
			Categoria cat = new Categoria();
			cat.setId(rs.getInt("id"));
			cat.setTipo(rs.getString("tipo"));
			listaCat.add(cat);
		}
		rs.close();
		ps.close();
		return listaCat;
	}

}
